<?php
class ModelNewsCategory extends Model {
	public function addCategory($data) {
		$sql  = " INSERT INTO " . DB_PREFIX . "news_category ";
		$sql .= " SET `top` = '" . (isset($data['top']) ? (int)$data['top'] : 0) . "'";
		$sql .= " , `column` = '" . (int)$data['column'] . "'";
		$sql .= " , sort_order = '" . (int)$data['sort_order'] . "'";
		$sql .= " , status = '" . (int)$data['status'] . "'";
		$sql .= " , type_id = '" . (int)$data['type_id'] . "'";
		$sql .= " , date_modified = NOW(), date_added = NOW()";
		$this->db->query($sql);

		$category_id = $this->db->getLastId();
		
		foreach ($data['category_description'] as $language_id => $value) {
			$sql_1  = " INSERT INTO " . DB_PREFIX . "news_category_description SET ";
			$sql_1 .= " category_id = '" . (int)$category_id . "'";
			$sql_1 .= " , language_id = '" . (int)$language_id . "'";
			$sql_1 .= " , name = '" . $this->db->escape($value['name']) . "'";
			$sql_1 .= " , meta_keyword = '" . $this->db->escape($value['meta_keyword']) . "'";
			$sql_1 .= " , meta_description = '" . $this->db->escape($value['meta_description']) ."'";
			$this->db->query($sql_1);
		}
		
		$restaurant_id = $this->restaurant->getRestaurantId() == null? '0' : $this->restaurant->getRestaurantId();
		$sql_2  = " INSERT INTO " . DB_PREFIX . "news_category_to_restaurant SET ";
		$sql_2 .= " category_id = '" . (int)$category_id . "'";
		$sql_2 .= " , restaurant_id = '" . (int)$restaurant_id . "'";
		$this->db->query($sql_2);

		if ($data['keyword']) {
			$sql_3  = " INSERT INTO " . DB_PREFIX . "url_alias SET ";
			$sql_3 .= " query = 'category_id=" . (int)$category_id . "'";
			$sql_3 .= " , keyword = '" . $this->db->escape($data['keyword']) . "'";
			$this->db->query($sql_3);
		}

		$this->cache->delete('news_category');
	}

	public function editCategory($category_id, $data) {
		$sql  = " UPDATE " . DB_PREFIX . "news_category SET `top` = '" . (isset($data['top']) ? (int)$data['top'] : 0) . "'";
		$sql .= " , `column` = '" . (int)$data['column'] . "'";
		$sql .= " , sort_order = '" . (int)$data['sort_order'] . "'";
		$sql .= " , status = '" . (int)$data['status'] . "'";
		$sql .= " , type_id = '" . (int)$data['type_id'] . "'";
		$sql .= " , date_modified = NOW()"; 
		$sql .= " WHERE category_id = '" . (int)$category_id . "'";
		
		$this->db->query($sql);
		
		$this->db->query("DELETE FROM " . DB_PREFIX . "news_category_description WHERE category_id = '" . (int)$category_id . "'");
		foreach ($data['category_description'] as $language_id => $value) {
			$sql_1  = " INSERT INTO " . DB_PREFIX . "news_category_description SET ";
			$sql_1 .= " category_id = '" . (int)$category_id . "'";
			$sql_1 .= " , language_id = '" . (int)$language_id . "'";
			$sql_1 .= " , name = '" . $this->db->escape($value['name']) . "'";
			$sql_1 .= " , meta_keyword = '" . $this->db->escape($value['meta_keyword']) . "'";
			$sql_1 .= " , meta_description = '" . $this->db->escape($value['meta_description']) ."'";
			$this->db->query($sql_1);
		}

		$this->db->query("DELETE FROM " . DB_PREFIX . "news_category_to_restaurant WHERE category_id = '" . (int)$category_id . "'");
		$restaurant_id = $this->restaurant->getRestaurantId() == null? '0' : $this->restaurant->getRestaurantId();
		$sql_2  = " INSERT INTO " . DB_PREFIX . "news_category_to_restaurant SET ";
		$sql_2 .= " category_id = '" . (int)$category_id . "'";
		$sql_2 .= " , restaurant_id = '" . (int)$restaurant_id . "'";
		$this->db->query($sql_2);


		$this->db->query("DELETE FROM " . DB_PREFIX . "url_alias WHERE query = 'category_id=" . (int)$category_id. "'");
		if ($data['keyword']) {
			$sql_3  = " INSERT INTO " . DB_PREFIX . "url_alias SET ";
			$sql_3 .= " query = 'category_id=" . (int)$category_id . "'";
			$sql_3 .= " , keyword = '" . $this->db->escape($data['keyword']) . "'";
			$this->db->query($sql_3);
		}

		$this->cache->delete('news_category');
	}

	public function deleteCategory($category_id) {
		$this->db->query("DELETE FROM " . DB_PREFIX . "news_category WHERE category_id = '" . (int)$category_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "news_category_description WHERE category_id = '" . (int)$category_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "news_category_to_restaurant WHERE category_id = '" . (int)$category_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "url_alias WHERE query = 'category_id=" . (int)$category_id . "'");

		$this->cache->delete('news_category');
	}

	public function getCategory($category_id) {
		$restaurant_id = $this->restaurant->getRestaurantId() == null? '0' : $this->restaurant->getRestaurantId();
		
		$sql  = " SELECT DISTINCT nc.category_id, nc.type_id, nc.top, nc.`column`, ";
		$sql .= " nc.sort_order, nc.`status`,( ";
		$sql .= " 	SELECT keyword FROM " . DB_PREFIX . "url_alias ";
		$sql .= " 	WHERE query = 'category_id=" . (int)$category_id . "') AS keyword " ; 
		$sql .= " FROM " . DB_PREFIX . "news_category nc"; 
		$sql .= " INNER JOIN " . DB_PREFIX . "news_category_to_restaurant ncts ";
		$sql .= "	ON nc.category_id = ncts.category_id";
		$sql .= " WHERE ncts.restaurant_id = " . (int)$restaurant_id ;
		$sql .= " AND nc.category_id = " . (int)$category_id;
		$query = $this->db->query($sql);

		return $query->row;
	}

	public function getCategories($data = array()) {	
		$language_id = (int)$this->config->get('config_language_id');	
		$restaurant_id = $this->restaurant->getRestaurantId() == null? '0' : $this->restaurant->getRestaurantId();	
		//$category_data = $this->cache->get('news.category.' . $language_id . '.' . (int)$restaurant_id . '.' . (int)$data['parent_id']);
						
		$sql  = " SELECT c.category_id, cd.`name`, c.type_id, c.`status`, c.sort_order, c.type_id ";
		$sql .= " FROM " . DB_PREFIX . "news_category c"; 
		$sql .= " LEFT JOIN " . DB_PREFIX . "news_category_description cd ON (c.category_id = cd.category_id)";
		$sql .= " LEFT JOIN " . DB_PREFIX . "news_category_to_restaurant ncts ON c.category_id = ncts.category_id";
		$sql .= " WHERE cd.language_id 	= '" . $language_id . "'";
		$sql .= "	AND restaurant_id 	= '" . (int)$restaurant_id . "'" ;
		
		if (!empty($data['filter_name'])) {
			$sql .= " AND LCASE(cd.`name`) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "%'";
		}
 
		if (isset($data['filter_type']) && !is_null($data['filter_type']) && $data['filter_type'] != '*') {
			$sql .= " AND c.type_id = '" . (int)$data['filter_type'] . "'";
		}
		
		if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
			$sql .= " AND c.`status` = '" . (int)$data['filter_status'] . "'";
		}
		
		$sort_data = array('cd.`name`', 'c.type_id', 'c.status', 'c.sort_order');	
		
		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			$sql .= " ORDER BY " . $data['sort'];	
		} else {
			$sql .= " ORDER BY cd.`name`";	
		}
		
		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}
		
		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}				

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}	
		
			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}
		
		$query = $this->db->query($sql);
		
		return $query->rows;
	}

	public function getTotalCategory($data){
		$restaurant_id = $this->restaurant->getRestaurantId() == null? '0' : $this->restaurant->getRestaurantId();
		$sql  = " SELECT COUNT(DISTINCT cd.category_id) AS total ";
		$sql .= " FROM " . DB_PREFIX . "news_category c"; 
		$sql .= " LEFT JOIN " . DB_PREFIX . "news_category_description cd ON (c.category_id = cd.category_id)";
		$sql .= " LEFT JOIN " . DB_PREFIX . "news_category_to_restaurant ncts ON c.category_id = ncts.category_id";
		$sql .= " WHERE cd.language_id 	= '" . (int)$this->config->get('config_language_id') . "'";
		$sql .= "	AND restaurant_id 	= '" . (int)$restaurant_id . "'" ;
		
		if (!empty($data['filter_name'])) {
			$sql .= " AND LCASE(cd.`name`) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "%'";
		}
 
		if (isset($data['filter_type']) && !is_null($data['filter_type']) && $data['filter_type'] != '*') {
			$sql .= " AND c.type_id = '" . (int)$data['filter_type'] . "'";
		}
		
		if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
			$sql .= " AND c.`status` = '" . (int)$data['filter_status'] . "'";
		}
		
		$query = $this->db->query($sql);
		
		return $query->row['total'];
	}
	
	public function getCategoryDescriptions($category_id) {
		$sql = "SELECT * FROM " . DB_PREFIX . "news_category_description WHERE category_id = '" . (int)$category_id . "'";
		$query = $this->db->query($sql);
		$category_description_data = array();
		foreach ($query->rows as $result) {
			$category_description_data[$result['language_id']] = array(
				'name'             => $result['name'],
				'meta_keyword'     => $result['meta_keyword'],
				'meta_description' => $result['meta_description']
			);
		}
		return $category_description_data;
	}
	
	public function getParentList($category_id = null) {
		$sql   = " SELECT rnc.category_id, rncd.`name`";
		$sql  .= " FROM ". DB_PREFIX . "news_category rnc";
		$sql  .= " INNER JOIN ". DB_PREFIX . "news_category_description rncd"; 
		$sql  .= " ON rnc.category_id = rncd.category_id";
		$sql  .= " WHERE  rncd.language_id ='" . (int)$this->config->get('config_language_id') ."'";
		
		if (isset($category_id) && !is_null($category_id)) {
			$sql  .= " AND rnc.category_id != '". (int)$category_id ."'";
			$sql  .= " AND rnc.parent_id != '". (int)$category_id ."'";
		}
		
		$query = $this->db->query($sql);
		
		return $query->rows;
	}
	
	public function getAutocompleteCategoryName($filter_name){
		$restaurant_id = $this->restaurant->getRestaurantId() == null? '0' : $this->restaurant->getRestaurantId();
		$sql   = " SELECT rncd.category_id, rncd.`name` AS category_name";
		$sql  .= " FROM ". DB_PREFIX . "news_category_description rncd ";
		$sql  .= " INNER JOIN ". DB_PREFIX . "news_category_to_restaurant rncts ON rncd.category_id = rncts.category_id";
		$sql  .= " WHERE rncts.restaurant_id = '". (int)$restaurant_id ."'"; 
		$sql  .= " AND rncd.language_id = '". (int)$this->config->get('config_language_id') ."'";
		$sql  .= " AND LCASE(rncd.`name`) LIKE '" . $this->db->escape(utf8_strtolower($filter_name)) . "%'";
		$query = $this->db->query($sql);

		return $query->rows;
	}
	
	public function getCategoryByNewTypeList($data = array()) {			
		$language_id = (int)$this->config->get('config_language_id');	
		$restaurant_id = $this->restaurant->getRestaurantId() == null? '0' : $this->restaurant->getRestaurantId();
		$cache_key = 'news.category.' . $language_id . '.' . (int)$restaurant_id. '.' . (int)$data['type_id'] . '.' . (int)$data['parent_id'];	
		$category_data = $this->cache->get($cache_key);
		
		if (!$category_data) {				
			$sql  = " SELECT c.category_id, cd.`name` FROM " . DB_PREFIX . "news_category c "; 
			$sql .= " LEFT JOIN " . DB_PREFIX . "news_category_description cd ON (c.category_id = cd.category_id) ";
			$sql .= " INNER JOIN " . DB_PREFIX . "news_category_to_restaurant ncts ON c.category_id = ncts.category_id ";
			$sql .= " WHERE c.parent_id = '" . ((int)$data['parent_id']) . "' ";
			$sql .= "	AND cd.language_id = '" . $language_id . "' ";
			$sql .= "	AND restaurant_id = " . (int)$restaurant_id ;
			$sql .= " 	AND c.`status` = '1'";
			$sql .= " 	AND c.type_id = '" . (int)$data['type_id'] . "'";
			$sql .= " ORDER BY cd.name ASC ";
			
			$query = $this->db->query($sql);
			
			$category_data = array();		
			foreach ($query->rows as $result) {
				$category_data[] = array(
					'category_id' 	=> $result['category_id'],
					'category_name'	=> $this->getPath($result['category_id'], $language_id)
				);
				$data['parent_id'] = $result['category_id'];
				$category_data = array_merge($category_data, $this->getCategoryList($data));
			}
			$this->cache->set($cache_key, $category_data);
		}
			
		return $category_data;
	}
	
}
?>